<HTML>
    <HEAD>
        <TITLE>Genesys Team Report ver 2.0. (c)Pavel Usim</TITLE>
        <link rel="stylesheet" type="text/css" href="..\def.css" />
    </HEAD>
    <BODY>
        <img align ="right" alt ="Genesys" src="..\img\GenesysLogo.png">
        <p align="center"><a name="top">Genesys Team Report. ver 2.0</a>
            <img src="..\img\beta.gif" alt="beta"><br>Admin Interface</p>

        <?php
        /*
          Admin Interface
          @author Koldun
         */

        require '..\dbconnect.php';

//  TODO: empty values / unexpected values (numeric in strings, and vice versa)
//        new/changes from last request (required a separate (memory) table)
//        requests, timing
//  TODO: Security Audits: by IP, hack attempts;
//  TODO: mysql> select sr, tse_name, count(*) from custsat, tse where sr_owner = owner_id group by sr having count(*) > 1 order by tse_name;
//  TODO: mysql> select sr, tse_name, count(*) from kcs, tse where linked_by = owner_id group by sr having count(*) > 6;
//  TODO: mysql> select customer.val, product.val, count(product.val) from srs, customer, product where customer.id = srs.customer and customer.val like 'Timbrook%' and srs.product = product.id group by product.val order by count(product.val);
//  TODO: most popular kcs docs for the last month... top 10...

        echo '<li><a href=..\index.php>MAIN PAGE</a>';
// new data processing
        echo '<li><a href=adminka.php>process source files</a>';

// stats on SR's
        echo '<p><li><b>Database Stats:</b></p>';
        $query = 'select min(sr), max(sr), count(sr) from srs';
        $result = mysql_query($query);
        if ($result) {
            $row = mysql_fetch_assoc($result);
            mysql_free_result($result);
            echo 'min SR# <b>' . $row['min(sr)'] . '</b><br>';
            echo 'max SR# <b>' . $row['max(sr)'] . '</b><br>';
            echo ' SR\'s in the database: <b>' . $row['count(sr)'] . '</b><br>';
        }
// TSE report
        $query = 'select count(tse_name), max(owner_id) from tse';
        $result = mysql_query($query);
        $row = mysql_fetch_assoc($result);
        mysql_free_result($result);
        echo 'TSE\'s in the database: <b>' . $row['count(tse_name)'] . '</b> (' . $row['max(owner_id)'] . ')<br>';

// kcs report
        $query = 'select count(doc_id) from kcs';
        $result = mysql_query($query);
        $row = mysql_fetch_assoc($result);
        mysql_free_result($result);
        echo 'Linked KCS in the database: <b>' . $row['count(doc_id)'] . '</b><br>';

//  cust sat report
        $query = 'select count(sr) from custsat';
        $result = mysql_query($query);
        $row = mysql_fetch_assoc($result);
        mysql_free_result($result);
        echo 'Surveys in the database: <b>' . $row['count(sr)'] . '</b><br>';

// dictionary table stats

        echo '<p><li><b>Global Stats</b></p>';

        $query = 'select count(id) from srstatus';
        $result = mysql_query($query);
        $row = mysql_fetch_assoc($result);
        echo '<li>SR Statuses: <b>' . $row['count(id)'] . '</b>';
        mysql_free_result($result);
        if ($row['count(id)'] > 0) {

            // TODO: for each srstatus get amount of SRS for the status
            // build a table with results (be region/team (with T3))
        }

        $result = mysql_query('select count(id) from srsubstatus');
        $row = mysql_fetch_assoc($result);
        echo '<li>SR Sub Statuses: <b>' . $row['count(id)'] . '</b>';
        mysql_free_result($result);
        if ($row['count(id)'] > 0) {

            // TODO: for each sr SUB status get amount of SRS for the status
            // build a table with results (be region/team (with T3))
        }

        // TODO: a separate report by customers will be build (top 100 /surveys/T3/products/Engineers/Priorities/Res_code)
        $result = mysql_query('select count(id) from staccount');
        $row = mysql_fetch_assoc($result);
        echo '<li>Sold To Accounts: <b>' . $row['count(id)'] . '</b>';
        mysql_free_result($result);

        $result = mysql_query('select count(id) from euaccount');
        $row = mysql_fetch_assoc($result);
        echo '<li>End User Accounts: <b>' . $row['count(id)'] . '</b>';
        mysql_free_result($result);

        $result = mysql_query('select count(id) from customer');
        $row = mysql_fetch_assoc($result);
        echo '<li>Customers: <b>' . $row['count(id)'] . '</b>';
        mysql_free_result($result);

        $result = mysql_query('select count(id) from resolcode');
        $row = mysql_fetch_assoc($result);
        echo '<li>Resolution Codes: <b>' . $row['count(id)'] . '</b>';
        mysql_free_result($result);

        $result = mysql_query('select count(id) from link');
        $row = mysql_fetch_assoc($result);
        echo '<li>Links: <b>' . $row['count(id)'] . '</b>';
        mysql_free_result($result);

        $result = mysql_query('select count(id) from os');
        $row = mysql_fetch_assoc($result);
        echo '<li>OS\'s: <b>' . $row['count(id)'] . '</b>';
        mysql_free_result($result);

        $result = mysql_query('select count(id) from switch');
        $row = mysql_fetch_assoc($result);
        echo '<li>Switches: <b>' . $row['count(id)'] . '</b>';
        mysql_free_result($result);

        $result = mysql_query('select count(id) from dbms');
        $row = mysql_fetch_assoc($result);
        echo '<li>DBMS\'s: <b>' . $row['count(id)'] . '</b>';
        mysql_free_result($result);

        $result = mysql_query('select count(id) from product');
        $row = mysql_fetch_assoc($result);
        echo '<li>Products: <b>' . $row['count(id)'] . '</b>';
        mysql_free_result($result);

        $result = mysql_query('select count(id) from productline');
        $row = mysql_fetch_assoc($result);
        echo '<li>Product Lines: <b>' . $row['count(id)'] . '</b>';
        mysql_free_result($result);

        $result = mysql_query('select count(id) from subarea');
        $row = mysql_fetch_assoc($result);
        echo '<li>Sub Areas: <b>' . $row['count(id)'] . '</b>';
        mysql_free_result($result);

        $result = mysql_query('select count(id) from supportgroup');
        $row = mysql_fetch_assoc($result);
        echo '<li>Support Groupes: <b>' . $row['count(id)'] . '</b>';
        mysql_free_result($result);

// log of requests:        
        $query = 'select time, ip, host, since, till, type, req from log where time > ' . (time() - 60 * 60 * 24 * 7 );
        @ $result = mysql_query($query);
        if (mysql_num_rows($result) > 0) {
            echo '<p><li><b>Requests</b><p>';
            echo '<table><tr><td><b>Time</b></td><td><b>IP</b></td><td><b>host name</b></td>
                <td><b>since</b></td><td><b>till</b></td><td><b>type</b></td>
                <td><b>Requested Engineers</b></td></tr>';
            while (@ $row = mysql_fetch_assoc($result)) {
                echo '<tr>';
                echo '<td>' . date("d.m.y H:i:s",$row['time']) . '</td>';
                echo '<td>' . $row['ip'] . '</td>';
                echo '<td>' . $row['host'] . '</td>';
                echo '<td>' . date("d.m.Y",$row['since']) . '</td>';
                echo '<td>' . date("d.m.Y",$row['till']) . '</td>';
                echo '<td>' . $row['type'] . '</td>';
                echo '<td>' . $row['req'] . '</td>';
                echo '</tr>';
            }
            echo '</table>';
        }
        @ mysql_free_result($result);

// team administration
        echo '<p><li><b>Team administration</b></p>';
        echo '<form name="modifyteams" action="updateteam.php" method=POST>';
        $result = mysql_query('select team, count(tse_name) from tse group by team order by team');
        echo '<table><TR>';
        while ($row = mysql_fetch_assoc($result)) {
            echo '<TD class="left">' . $row['team'] . ' team (' . $row['count(tse_name)'] . ')</TD>';
            $query = 'select owner_id, tse_name from tse where team = \'' . $row['team'] . '\' order by tse_name';
            $result1 = mysql_query($query);
            $tablestring .= '<TD class="left">';
            while ($row1 = mysql_fetch_assoc($result1))
                $tablestring .= $row1['tse_name'] . ' <input name=\'' . $row1['owner_id'] . '\' type=\'text\', size = 1, maxlength = 3, value=\'' . $row['team'] . '\'></br>';

            $tablestring .= '</TD>';
        }
        echo '</TR><TR class="left">' . $tablestring . '</TR></table>';
        echo '<input type="submit" value="submit">';
        echo '</form>';

        echo '<li><a href=cleandb.php>recreate the database</a>';
        echo '<li><a href=..\index.php>MAIN PAGE</a>';
        mysql_close($conn);
        ?>
    </BODY>
</HTML>